-- 浙江代理区机器人推送 签收滞留汇总
with network_detail as (
    select code,
           name,
           virt_code,
           virt_name,
           regional_id,
           regional_desc,
           agent_code,
           agent_name,
           fran_code,
           fran_name,
           provider_id,
           provider_desc,
           city_id,
           city_desc,
           zone_id,
           zone_code,
           zone_name,
           zone_staff_code,
           zone_staff_name,
           area_id,
           area_desc --区县
    from jms_dim.dim_network_whole_massage
    where is_enable = 1
      and is_delete = 1
      and network_type = 6
     and name  not like 'BEST%'
),
     terminal_sign_punctuality_rate_dt_tmp as (
   select '{{ execution_date  | cst_ds }}' as dt,
                final_sign_network_code,
                max(final_sign_network_name) as final_sign_network_name,
                sum(if(dt='{{ execution_date  | cst_ds }}',need_sign_count,0))         as day_need_sign_count,
                sum(if(dt='{{ execution_date  | cst_ds }}',all_sign_22,0))             as day_all_sign_22,
                sum(if(dt='{{ execution_date  | cst_ds }}',all_sign_24,0))             as day_all_sign_24,
                sum(if(dt='{{ execution_date  | cst_ds }}',need_sign_cnt_1030,0))      as day_need_sign_cnt_1030,
                sum(if(dt='{{ execution_date  | cst_ds }}',aging_sign_cnt_1030,0))     as day_aging_sign_cnt_1030,
                sum(if(dt='{{ execution_date  | cst_ds }}',need_sign_cnt_1130,0))      as day_need_sign_cnt_1130,
                sum(if(dt='{{ execution_date  | cst_ds }}',aging_sign_cnt_1130,0))     as day_aging_sign_cnt_1130,
                sum(if(dt='{{ execution_date  | cst_ds }}',need_sign_cnt_1400,0))      as day_need_sign_cnt_1400,
                sum(if(dt='{{ execution_date  | cst_ds }}',aging_sign_cnt_1400,0))     as day_aging_sign_cnt_1400,
                sum(if(dt='{{ execution_date  | cst_ds }}',need_sign_cnt_1800,0))      as day_need_sign_cnt_1800,
                sum(if(dt='{{ execution_date  | cst_ds }}',aging_sign_cnt_1800,0))     as day_aging_sign_cnt_1800,
                sum(if(dt='{{ execution_date  | cst_ds }}',need_sign_cnt_2200,0))      as day_need_sign_cnt_2200,
                sum(if(dt='{{ execution_date  | cst_ds }}',aging_sign_cnt_2200,0))     as day_aging_sign_cnt_2200,

                sum(if(dt=date_add('{{ execution_date  | cst_ds }}', -1),need_sign_count,0))         as yesterday_need_sign_count,
                sum(if(dt=date_add('{{ execution_date  | cst_ds }}', -1),all_sign_22,0))             as yesterday_all_sign_22,
                sum(if(dt=date_add('{{ execution_date  | cst_ds }}', -1),all_sign_24,0))             as yesterday_all_sign_24
         from jms_dm.dm_terminal_sign_punctuality_rate_dt
         where dt >= date_add('{{ execution_date  | cst_ds }}', -1)
           and dt <= '{{ execution_date  | cst_ds }}'
         -- and final_sign_network_code='6371133'
         group by final_sign_network_code),
     terminal_sign_punctuality_rate as (
         select temp1.*,
                round((Fine_1030+fine_1130+Fine_1400+Fine_1800+fine_2200),4) as fine_sum,
                if(day_sign_rate>=93.0,round((Fine_1030+fine_1130+Fine_1400+Fine_1800+fine_2200)*0.8,4),0) as reback_fine,
                if(day_sign_rate>=93.0,round((Fine_1030+fine_1130+Fine_1400+Fine_1800+fine_2200)*0.2,4),round((Fine_1030+fine_1130+Fine_1400+Fine_1800+fine_2200),4)) as last_fine
         from (
                  select tmp.*
                       , case
                             when (t4.ten_half_target - day_sign_rate_1030)/100 * day_need_sign_cnt_1030 < 100.0 then 0
                             when (t4.ten_half_target - day_sign_rate_1030)/100 * day_need_sign_cnt_1030 * t4.ten_half_one_fine + t4.ten_half_fix_fee >= t4.ten_half_max_fine then t4.ten_half_max_fine
                             else (t4.ten_half_target - day_sign_rate_1030)/100*  day_need_sign_cnt_1030 *  t4.ten_half_one_fine + t4.ten_half_fix_fee
                             end       as Fine_1030

                       , case
                             when (t4.eleven_half_target - day_sign_rate_1130) /100 * day_need_sign_cnt_1130 < 100.0 then 0
                             when (t4.eleven_half_target - day_sign_rate_1130)/100 *  day_need_sign_cnt_1130 * t4.eleven_half_one_fine + t4.eleven_half_fix_fee >= t4.eleven_half_max_fine then t4.eleven_half_max_fine
                             else (t4.eleven_half_target - day_sign_rate_1130)/100 *  day_need_sign_cnt_1130 * t4.eleven_half_one_fine + t4.eleven_half_fix_fee
                              end as Fine_1130

                       , case
                             when (t4.fourteen_target - day_sign_rate_1400) /100* day_need_sign_cnt_1400 < 100.0 then 0
                             when (t4.fourteen_target - day_sign_rate_1400)/100 * day_need_sign_cnt_1400 *
                                  t4.fourteen_one_fine + t4.fourteen_fix_fee >= t4.fourteen_max_fine
                                 then t4.fourteen_max_fine
                             else (t4.fourteen_target - day_sign_rate_1400)/100 * day_need_sign_cnt_1400 *
                                  t4.fourteen_one_fine + t4.fourteen_fix_fee end       as Fine_1400

                       , case
                             when (t4.eighteen_target - day_sign_rate_1800)/100 * day_need_sign_cnt_1800 < 100.0 then 0
                             when (t4.eighteen_target - day_sign_rate_1800) /100* day_need_sign_cnt_1800 *
                                  t4.eighteen_one_fine + t4.eighteen_fix_fee >= t4.eighteen_max_fine
                                 then t4.eighteen_max_fine
                             else (t4.eighteen_target - day_sign_rate_1800)/100 * day_need_sign_cnt_1800 *
                                  t4.eighteen_one_fine + t4.eighteen_fix_fee end       as Fine_1800
                       , case
                             when (t4.twenty_two_target - day_sign_rate_2200)/100 * day_need_sign_cnt_2200 < 100.0 then 0
                             when (t4.twenty_two_target - day_sign_rate_2200)/100 * day_need_sign_cnt_2200 *
                                  t4.twenty_two_one_fine + t4.twenty_two_fix_fee >= t4.twenty_two_max_fine
                                 then t4.twenty_two_max_fine
                             else (t4.twenty_two_target - day_sign_rate_2200)/100 * day_need_sign_cnt_2200 *
                                  t4.twenty_two_one_fine + t4.twenty_two_fix_fee end   as Fine_2200
                  from (
                           select final_sign_network_code,
                                  final_sign_network_name,
                                  cast(day_need_sign_count as int)as day_need_sign_count,
                                  cast(day_all_sign_24 as int)as day_all_sign_24,
                                  cast(day_all_sign_22 as int)as day_all_sign_22,
                                  round(day_all_sign_22 *100/ day_need_sign_count, 2)                  as day_sign_rate,
                                  cast(yesterday_need_sign_count as int)as yesterday_need_sign_count,
                                  cast(yesterday_all_sign_24 as int)as yesterday_all_sign_24,
                                  cast(yesterday_all_sign_22 as int)as yesterday_all_sign_22,
                                  if(yesterday_need_sign_count=0,0,round(yesterday_all_sign_22*100 / yesterday_need_sign_count, 2))                  as yesterday_sign_rate,
                                  (day_need_sign_count - yesterday_need_sign_count)                      as need_sign_qoq, --应签收环比
                                  day_need_sign_cnt_1030,
                                  day_aging_sign_cnt_1030,
                                  if(day_need_sign_cnt_1030=0,0,round(day_aging_sign_cnt_1030 * 100 / day_need_sign_cnt_1030, 2)) as day_sign_rate_1030,
                                  day_need_sign_cnt_1130,
                                  day_aging_sign_cnt_1130,
                                  if(day_need_sign_cnt_1130=0,0,round(day_aging_sign_cnt_1130 * 100 / day_need_sign_cnt_1130, 2)) as day_sign_rate_1130,
                                  day_need_sign_cnt_1400,
                                  day_aging_sign_cnt_1400,
                                  if(day_need_sign_cnt_1400=0,0,round(day_aging_sign_cnt_1400 * 100 / day_need_sign_cnt_1400, 2)) as day_sign_rate_1400,
                                  day_need_sign_cnt_1800,
                                  day_aging_sign_cnt_1800,
                                  if(day_need_sign_cnt_1800=0,0,round(day_aging_sign_cnt_1800 * 100 / day_need_sign_cnt_1800, 2)) as day_sign_rate_1800,
                                  day_need_sign_cnt_2200,
                                  day_aging_sign_cnt_2200,
                                  if(day_need_sign_cnt_2200=0,0,round(day_aging_sign_cnt_2200 * 100 / day_need_sign_cnt_2200, 2)) as day_sign_rate_2200,
                                  dt
                           from terminal_sign_punctuality_rate_dt_tmp
                       ) tmp
                           cross join (select ten_half_fix_fee     --10:30固定费用
                                            , ten_half_target      -- 10:30目标值(%)
                                            , ten_half_one_fine    -- 10:30单票罚款金额
                                            , ten_half_max_fine    --  10:30最高罚款金额
                                            , ten_half_min_fine    -- 10:30最低罚款票数
                                            , eleven_half_fix_fee  -- 11:30固定费用
                                            , eleven_half_target   -- 11:30目标值(%
                                            , eleven_half_one_fine --11:30单票罚款金
                                            , eleven_half_max_fine --11:30最高罚款金额
                                            , eleven_half_min_fine --11:30最低罚款票数
                                            , fourteen_fix_fee     --14:00固定费用
                                            , fourteen_target      -- 14:00目标值(%)
                                            , fourteen_one_fine    -- 14:00单票罚款金额
                                            , fourteen_max_fine    -- 14:00最高罚款金额
                                            , fourteen_min_fine    -- 14:00最低罚款票数
                                            , eighteen_fix_fee     -- 18:00固定费用
                                            , eighteen_target      -- 18:00目标值(%)
                                            , eighteen_one_fine    -- 18:00单票罚款金额
                                            , eighteen_max_fine    -- 18:00最高罚款金额
                                            , eighteen_min_fine    -- 18:00最低罚款票数
                                            , twenty_two_fix_fee   -- 22:00固定费用
                                            , twenty_two_target    --  22:00目标值(%)
                                            , twenty_two_one_fine  --22:00单票罚款金额
                                            , twenty_two_max_fine  -- 22:00最高罚款金额
                                            , twenty_two_min_fine  -- 22:00最低罚款票数'
                                       from jms_dim.dim_tab_fee_config_base
                                       where menu = '时效签收准点率'
                                         and start_date <= '{{ execution_date  | cst_ds }}'
                                         and end_date >= '{{ execution_date  | cst_ds }}'
                                         and status = 1) t4
              )temp1
     ),
     network_retention as (
         select final_sign_network_code,
                max(final_sign_network_name)                  as final_sign_network_name,
                is_contain_difficult, --是否包含问题件
                cast(sum(retention_count_1d) as int)                       as retention_count_1d,
                cast(sum(retention_count_3d) as int)                       as retention_count_3d,
                cast(sum(retention_count_5d) as int)                       as retention_count_5d,
                cast(sum(retention_count_7d) as int)                       as retention_count_7d,
                cast(sum(retention_count_20d) as int)                     as retention_count_20d,
                cast(sum(retention_over_20d)  as int)                     as retention_over_20d,
                cast(sum(retention_count_20d + retention_over_20d) as int) as retention_sum
         from jms_dm.dm_network_retention_summary_dt
         where dt = '{{ execution_date  | cst_ds }}'
         group by final_sign_network_code, is_contain_difficult)
insert overwrite table jms_dm.dm_terminal_sign_union_retention_network_sum_dt partition (dt)
-- 末端签收和滞留网点汇总
select /* +BROADCAST(t3)*/
    t1.code,                      -- 网点code
    t1.name,                      --网点名字
    t1.virt_code,                 --虚拟代理区code
    t1.virt_name,                 --虚拟代理区
    t1.regional_id,               --管理大区id
    t1.regional_desc,             --管理大区
    t1.agent_code,                --代理区code
    t1.agent_name,                --代理区
    t1.fran_code,                 --加盟商code
    t1.fran_name,                 --加盟商
    t1.provider_id,               --省份id
    t1.provider_desc,             --省份
    t1.city_id,                   -- 城市id
    t1.city_desc,                 --城市
    t1.zone_id,                   --片区id
    t1.zone_code,                 --片区
    t1.zone_name,                 --片区
    t1.area_id,                   --区县id
    t1.area_desc,                 --区县
    t1.zone_staff_code,           --片区负责人编码
    t1.zone_staff_name,           --片区负责人
    t2.day_all_sign_22,           --22点签收量
    t2.day_all_sign_24,           --24点签收量
    t2.day_need_sign_count,       --应签收量
    t2.day_sign_rate,             --22点签收率
    t2.yesterday_need_sign_count, --昨天应签收量
    t2.yesterday_all_sign_22,     --昨天22点签收量
    t2.yesterday_all_sign_24,     --昨天24点签收量
    t2.yesterday_sign_rate,       --昨天22点签收率
    t2.need_sign_qoq,             --应签收环比
    t3.retention_count_1d,        -- 一天内滞留
    t3.retention_count_3d,        --三天内滞留量
    t3.retention_count_5d,        --五天内滞留
    t3.retention_count_7d,        --七天内滞留
    t3.retention_count_20d,       --20天内滞留
    t3.retention_over_20d,        --超20滞留
    t3.retention_sum,             --滞留总量（20+超20）
    t3.is_contain_difficult,      --是否包含问题件
    t2.last_fine,   -- 最终罚款
    t2.day_sign_rate_1030, -- 签收率1030
    t2.day_sign_rate_1130,-- 签收率1130
    t2.day_sign_rate_1400,-- 签收率1400
    t2.day_sign_rate_1800,-- 签收率1800
    t2.day_sign_rate_2200,-- 签收率2200
    '{{ execution_date  | cst_ds }}' as date_time, --业务日期
     round(fine_1030,4)as fine_1030,
     round(fine_1130,4)as fine_1130,
     round(fine_1400,4) as fine_1400,
     round(fine_1800,4)as fine_1800,
     round(fine_2200,4) as fine_2200,
       t2.fine_sum,  --总罚款
       t2.reback_fine,--返款
    '{{ execution_date  | cst_ds }}' as dt                         --日期
 from network_detail t1
 left join  terminal_sign_punctuality_rate t2
     on t1.code=t2.final_sign_network_code
 left join network_retention  t3
   on t1.code = t3.final_sign_network_code
distribute by 1
;